package com.jframe.sys.orm.service.impl;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.springframework.jdbc.core.JdbcTemplate;
import com.jframe.sys.orm.service.Service;
import com.jframe.sys.utils.JsonUtil;


public class ServiceImpl implements Service {

	protected Class pojoClz;

	protected JdbcTemplate jdbcTemplate;
	
	protected String databaseProductName ;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate; 
	}

	@Override
	public List findBySql(Class clz, String sql) {
		// TODO Auto-generated method stub
		List<Map<String,Object>> ls = this.jdbcTemplate.queryForList(sql);
		List lsT = new ArrayList();
		for (Map<String,Object> map : ls) {
			Object obj = this.map2Obj(clz, map);
			lsT.add(obj);
		}
		return lsT;
	}

	@Override
	public List findBySql(Class clz, String sql, Object obj) {
		// TODO Auto-generated method stub
		return this.findBySql(clz, sql, new Object[] { obj });
	}

	@Override
	public List findBySql(Class clz, String sql, Object[] objs) {
		// TODO Auto-generated method stub
		List<Map<String,Object>> ls = this.jdbcTemplate.queryForList(sql, objs);
		List lsT = new ArrayList();
		for (Map map : ls) {
			Object obj = this.map2Obj(clz, map);
			lsT.add(obj);
		}
		return lsT;
	}

	private Object map2Obj(Class clz, Map map) {

		Field[] fields = clz.getDeclaredFields();
		String objJson = "{";
		for (Field field : fields) {
			String name = field.getName();
			if (name.equals("serialVersionUID"))
				continue;
			objJson += "\"" + name + "\"  : \"" + map.get(name) + "\",";
		}
		objJson += "}";
		return JsonUtil.json2Obj(objJson, clz);
	}

	@Override
	public Map<String, String> ls2Map(List list, String keyField,
			String valueField) {
		// TODO Auto-generated method stub
		Map<String, String> map = new LinkedHashMap();
		for (int i = 0; i < list.size(); i++) {
			String objJson = JsonUtil.obj2Json(list.get(i));
			String key = JsonUtil.getJsonValue(objJson, keyField);
			String value = JsonUtil.getJsonValue(objJson, valueField);
			map.put(key, value);
		}
		return map;
	}

	public  int getListSize(List objs)
	{
		return objs ==null ? 0: objs.size();
	} 
	
	
	/**
	 * 获取记录�?
	 * @param sql
	 * @return
	 */
	public int getCountBySql(String sql){
		String sql2 = this.getCountSQL(sql);
		return this.jdbcTemplate.queryForInt(sql2);
	}
	
	private static String getCountSQL(String sql) {
		Matcher fMatcher = FROM_PATTERN.matcher(sql);
		if (!fMatcher.find()) {
			throw new IllegalArgumentException("no from clause found in query");
		}
		int fLoc = fMatcher.start(2);
		Matcher oMatcher = ORDER_PATTERN.matcher(sql);
		int oLoc = oMatcher.find() ? oMatcher.start(1) : sql.length();
		return "select count(*) " + sql.substring(fLoc, oLoc);
	}
  
  public String getDataBaseProductName(){
	  
	if( this.databaseProductName == null ){
	  try {
		   this.databaseProductName = this.jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName().replaceAll("/", "-");
	     } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	   }
	}
	return this.databaseProductName;
  }
	
  public String getSqlByPage(String sql ,int start,int limit){
		
	    String dbType = this.getDataBaseProductName();
		if(dbType.equalsIgnoreCase("oracle")){
			return this.getOracleSqlByPage(sql, start, limit);
		}
		else if(dbType.equalsIgnoreCase("mysql")){
			return this.getMySqlByPage(sql, start, limit);
		}
		else if(dbType.equalsIgnoreCase("db2")){
			return this.getDb2SqlByPage(sql, start, limit);
		}
		else if(dbType.equalsIgnoreCase("derby")){
			return this.getDerbySqlByPage(sql, start, limit);
		}
		else if(dbType.equalsIgnoreCase("sqlserver")){
			return this.getSqlServerSqlByPage(sql, start, limit);
		}
		else{
			return sql;
		}
	}
	
	private String getOracleSqlByPage(String sql,int start,int limit){		
		int rows = start + limit - 1;
		String sqlByPage = " select * from (select  sql.* ,rownum rid from (" + sql +  ")  sql  where rownum <= "+ rows +" ) where rid >= "+ start +" ";		
		return sqlByPage;
	}
	
	private String getSqlServerSqlByPage(String sql,int start,int limit){
				
		return sql;
	}
	
	private String getMySqlByPage(String sql,int start,int limit){
		if(start < 0 && limit < 0) return sql;
		start = start < 0 ? 0 :start;		
		sql += " limit " + start  ;
		sql += limit < 0 ? "" : " , " + limit;		
		return sql ;
	}
	
	private String getDb2SqlByPage(String sql,int start,int limit){
		
		String sqlByPage = " select * from ( select row_number() over() as rownum , sql.* from (" + sql + ") as sql ) as tmp where rownum > "
		   + start  + " rownum <=" + start + limit;
		return sqlByPage;
	}
	
	private String getDerbySqlByPage(String sql,int start,int limit){
		
		String sqlByPage = " select * from ( select row_number() over() as rownum , sql.* from (" + sql + ") as sql ) as tmp where rownum > "
		   + start  + " rownum <=" + start + limit;
		return sqlByPage;
	}
	
	// 取得 from 位置的表达式
	private static final Pattern FROM_PATTERN = Pattern.compile(
			"(^|\\s)(from)\\s", Pattern.CASE_INSENSITIVE);

	// 取得 order 位置的表达式
	private static final Pattern ORDER_PATTERN = Pattern.compile(
			"\\s(order)(\\s)+by\\s", Pattern.CASE_INSENSITIVE);

	@Override
	public List<Map<String, Object>> findListMapBySql(String sql) {
		// TODO Auto-generated method stub
		return this.jdbcTemplate.queryForList(sql);
	}
}
